using Azrng.DbOperator;
using Azrng.DbOperator.Helper;
using Common.Core.Helpers;
using Common.Extension;
using DBCHM.Models;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Security.AccessControl;
using System.Threading.Tasks;

namespace DBCHM.Service;

public class SqliteService : IStoreService
{
    /// <summary>
    /// 当前应用程序的名称
    /// </summary>
    private static string ConfigFileName =
        Path.GetFileNameWithoutExtension(AppDomain.CurrentDomain.FriendlyName).Replace(".vshost", "");

    /// <summary>
    /// 定义配置存放的路径
    /// </summary>
    private static string AppPath =
        Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData, Environment.SpecialFolderOption.Create),
            ConfigFileName);

    /// <summary>
    /// sqlite db文件的存放路径
    /// </summary>
    private static string ConfigFilePath = string.Empty;

    public async Task<bool> InitAsync()
    {
        try
        {
            // 需要处理超长目录了才需要安装：ZetaLongPaths
            if (!Directory.Exists(AppPath))
            {
                Directory.CreateDirectory(AppPath);
            }

            //AddSecurityControll2Folder(AppPath);
            ConfigFilePath = Path.Combine(AppPath, DbChmConst.DbName + ".db");

            //表不存在则创建 连接字符串 配置表
            var connectionStr = $"Data Source={ConfigFilePath}";
            var dbHelper = new SqliteDbHelper(connectionStr);

            var selectTableStr = "SELECT name as desc FROM sqlite_master WHERE type='table'";
            var tableList = await dbHelper.QueryAsync<string>(selectTableStr);
            if (tableList is null || !tableList.Contains(ConfigFileName))
            {
                var strSql = $@"create table {DbChmConst.TableName}
                 (
                    Id integer PRIMARY KEY autoincrement,
                    ConnectName nvarchar(200) unique,
                    DBType varchar(30),
                    Host varchar(100),
                    Port integer,
                    DataBase varchar(100),
                    Schema varchar(100),
                    UserName varchar(50),
                    Password varchar(100),
                    ConnTimeOut integer,
                    ConnString text,
                    AdditionalParameter text,
                    Modified text
                 )";
                await dbHelper.ExecuteAsync(strSql);
            }

            return true;
        }
        catch (Exception ex)
        {
            LocalLogHelper.WriteMyLogs("ERROR", "数据库初始化报错" + ex.Message);
            return false;
        }
    }

    public async Task<List<DbConnectConfigDto>> GetConfigListAsync()
    {
        var sql = $"select * from {DbChmConst.TableName}";
        var connectionStr = $"Data Source={ConfigFilePath}";
        var dbHelper = new SqliteDbHelper(connectionStr);
        return (await dbHelper.QueryAsync<DbConnectConfigDto>(sql)).ToList();
    }

    public async Task<bool> AddConfigAsync(DbConnectConfigDto dbConnectConfigDto)
    {
        var sql = $@"INSERT INTO {DbChmConst.TableName} (ConnectName, DBType, Host, Port, DataBase, Schema, UserName, Password, ConnTimeOut,
                      ConnString, AdditionalParameter, Modified)
VALUES (@ConnectName, @DBType, @Host, @Port, @DataBase, @Schema, @UserName, @Password, @ConnTimeOut, @ConnString, @AdditionalParameter, @Modified);";

        var connectString = GetConnectionString(dbConnectConfigDto.DbType, dbConnectConfigDto.Host, dbConnectConfigDto.Port,
            dbConnectConfigDto.DataBase, dbConnectConfigDto.UserName, dbConnectConfigDto.Password);

        //表不存在则创建 连接字符串 配置表
        var connectionStr = $"Data Source={ConfigFilePath}";
        var dbHelper = new SqliteDbHelper(connectionStr);
        return await dbHelper.ExecuteAsync(sql, new
                                                {
                                                    ConnectName = dbConnectConfigDto.ConnectName,
                                                    DBType = dbConnectConfigDto.DbType.ToString(),
                                                    Host = dbConnectConfigDto.Host,
                                                    Port = dbConnectConfigDto.Port,
                                                    DataBase = dbConnectConfigDto.DataBase,
                                                    Schema = dbConnectConfigDto.Schema,
                                                    UserName = dbConnectConfigDto.UserName,
                                                    Password = dbConnectConfigDto.Password,
                                                    ConnTimeOut = dbConnectConfigDto.Timeout,
                                                    ConnString = connectString,
                                                    AdditionalParameter = dbConnectConfigDto.AdditionalParameter,
                                                    Modified = DateTime.Now.ToNowDateTime().ToString(CultureInfo.CurrentCulture),
                                                }) >
               0;
    }

    public Task<bool> DeleteConfigAsync(int id)
    {
        throw new NotImplementedException();
    }

    public async Task<DbConnectConfigEntity> GetCurrentConfigAsync()
    {
        if (DbChmConst.CurrConnectId == 0)
        {
            throw new ArgumentException("当前未连接数据库");
        }

        var connectionStr = $"Data Source={ConfigFilePath}";
        var dbHelper = new SqliteDbHelper(connectionStr);
        var sql = $"select * from {DbChmConst.TableName} where Id=@id";
        var entity = await dbHelper.QueryFirstOrDefaultAsync<DbConnectConfigEntity>(sql, new { id = DbChmConst.CurrConnectId });

        entity.ConnString = GetConnectionString(entity.DbType, entity.Host, entity.Port,
            entity.DataBase, entity.UserName, entity.Password);
        return entity;
    }

    /// <summary>
    /// 获取连接字符串
    /// </summary>
    /// <param name="dbType">数据库类型</param>
    /// <param name="server">host地址</param>
    /// <param name="port">SqlServer默认端口：1433，MySql默认端口：3306，SqlServer默认端口：1433，Oracle默认端口：1521，PostgreSql默认端口：5432,DB2默认端口：50000</param>
    /// <param name="databBase">数据库名称</param>
    /// <param name="uid">用户名</param>
    /// <param name="pwd">密码</param>
    /// <param name="connTimeOut"></param>
    /// <param name="extraParam"></param>
    /// <returns>连接字符串</returns>
    private string GetConnectionString(DatabaseType dbType, string? server, int? port, string? databBase, string? uid,
                                       string pwd, int connTimeOut = 60, string extraParam = "")
    {
        server = (server ?? string.Empty).Trim();
        databBase = (databBase ?? string.Empty).Trim();
        uid = (uid ?? string.Empty).Trim();

        string connectionString;
        switch (dbType)
        {
            case DatabaseType.SqlServer:
                connectionString = string.Format(@"server={0}{1};database={2};uid={3};pwd={4};connection timeout={5}", server,
                    (port.HasValue ? ("," + port.Value) : string.Empty), databBase, uid, pwd, connTimeOut);
                break;
            case DatabaseType.MySql:
                connectionString =
                    string.Format(@"Server={0};{1}Database={2};User={3};Password={4};OldGuids=True;connection timeout={5};" + extraParam,
                        server, (port.HasValue ? ("Port=" + port.Value + ";") : string.Empty), databBase, uid, pwd,
                        connTimeOut);
                break;
            case DatabaseType.Oracle:
                connectionString =
                    $"Data Source={server}:{(port ?? 1521)}/{databBase};User Id={uid};password={pwd};Pooling=true;connection timeout={connTimeOut}";
                break;
            case DatabaseType.PostgresSql:
                connectionString =
                    $"host={server};{(port.HasValue ? ("port=" + port.Value + ";") : string.Empty)}database={databBase};username={uid};password={pwd};timeout={connTimeOut}";
                break;
            case DatabaseType.SqLite:
                //connectionString = string.Format("Data Source={0};Pooling=True;BinaryGUID=True;Enlist=N;Synchronous=Off;Journal Mode=WAL;Cache Size=5000;", databBase);
                connectionString = $"Data Source={databBase};";
                if (!string.IsNullOrWhiteSpace(pwd))
                {
                    connectionString += "version=3;password=" + pwd;
                }

                break;
            default:
                throw new ArgumentException("未知数据库类型！");
        }

        return connectionString;
    }

    // /// <summary>
    // ///为文件夹添加users，everyone用户组的完全控制权限
    // /// </summary>
    // /// <param name="dirPath"></param>
    // private static void AddSecurityControll2Folder(string dirPath)
    // {
    //     //获取文件夹信息
    //     var dir = new DirectoryInfo(dirPath);
    //     if (dir.Exists)
    //     {
    //         //获得该文件夹的所有访问权限
    //         var dirSecurity = dir.GetAccessControl(AccessControlSections.All);
    //
    //         //设定文件ACL继承
    //         var inherits = InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit;
    //
    //         //添加ereryone用户组的访问权限规则 完全控制权限
    //         var everyoneFileSystemAccessRule = new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, inherits,
    //             PropagationFlags.None, AccessControlType.Allow);
    //
    //         //添加Users用户组的访问权限规则 完全控制权限
    //         var usersFileSystemAccessRule = new FileSystemAccessRule("Users", FileSystemRights.FullControl, inherits, PropagationFlags.None,
    //             AccessControlType.Allow);
    //         var isModified = false;
    //         dirSecurity.ModifyAccessRule(AccessControlModification.Add, everyoneFileSystemAccessRule, out isModified);
    //         dirSecurity.ModifyAccessRule(AccessControlModification.Add, usersFileSystemAccessRule, out isModified);
    //
    //         //设置访问权限
    //         dir.SetAccessControl(dirSecurity);
    //     }
    // }
}